﻿using System;
using System.Data.Odbc;
using System.Data;
using personalplaner.core.errorhandling;
using personalplaner.business;
using personalplaner.business.common;

namespace personalplaner.core.sql
{
	public class Querry : IDisposable
	{
		DatabaseSettings settings;

		//OdbcConnection m_odbcConnection = null;
		OdbcCommand m_command;

		string m_commandString;
		int m_parameterCount = 0;
		string m_stpName;
        string m_loggstring;

		//static bool silentTransaction = false;
		///// <summary>
		///// will show a messagebox if an error occured if true
		///// </summary>
		//public static bool SilentTransaction
		//{
		//    get
		//    {
		//        return silentTransaction;
		//    }
		//    set
		//    {
		//        silentTransaction = value;
		//    }
		//}

		public Querry(DatabaseSettings settings)
		{
			this.settings = settings;

			CreateCommandObject();
		}

		public Querry(DatabaseSettings settings, string STPName)
			: this(settings)
		{
			SetSTPName(STPName);
		}

		#region IDisposable Members

		public void Dispose()
		{
			try
			{
				if (settings.Connection.State != ConnectionState.Closed)
					settings.Connection.Close();
			}
			catch
			{
			}
		}

		#endregion

		#region Parameters

		/// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
		/// <param name="direction"></param>
		/// <returns></returns>
		public OdbcParameter AddParameter(string parameterName, int value, ParameterDirection direction = ParameterDirection.Input)
        {
			return SetParameter(parameterName, value, OdbcType.Int, direction);
        }

		/// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
		/// <param name="direction"></param>
		/// <returns></returns>
		public OdbcParameter AddParameter(string parameterName, DateTime value, ParameterDirection direction = ParameterDirection.Input)
        {
			return AddParameter(parameterName, value.ToString("yyyyMMdd"), direction);
        }

		/// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
		/// <param name="direction"></param>
		/// <returns></returns>
		public OdbcParameter AddParameter(string parameterName, string value, ParameterDirection direction = ParameterDirection.Input)
		{
			return SetParameter(parameterName, value, OdbcType.VarChar, direction);
		}

		/// <summary>
		/// Adds a Parameter to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
		/// <param name="direction"></param>
		/// <returns></returns>
		public OdbcParameter AddParameter(string parameterName, double value, ParameterDirection direction = ParameterDirection.Input)
		{
			return SetParameter(parameterName, value, OdbcType.Double, direction);
		}

		/// <summary>
		/// Creates a new <see cref="OdbcParameter"/> and adds it to the SQLString
		/// </summary>
		/// <param name="parameterName">Name of Parameter</param>
		/// <param name="value">Value of Parameter</param>
		/// <param name="type"></param>
		/// <param name="direction"></param>
		/// <returns></returns>
		private OdbcParameter SetParameter(string parameterName, object value, OdbcType type, ParameterDirection direction)
		{
            if (value == null)
                value = "";

			// parameterName muss immer mit @ anfangen z.B. "@anstellungid"
			if (this.m_parameterCount == 0)
			{
				this.InitializeCommandString();
                m_loggstring = m_stpName + " " + value.ToString();
			}
			else
			{
				this.AddParameterToCmdString();
                m_loggstring += ", " + value.ToString();
			}

			var prm = new OdbcParameter(parameterName, type);//"@anstellungid"
			prm.Direction = ParameterDirection.Input;
			prm.Value = Convert.ToString(value);
            prm.Direction = direction;
			m_command.Parameters.Add(prm);
            
			m_parameterCount++;

			return prm;
		}


		private void AddParameterToCmdString()
		{
			this.m_commandString = m_commandString + ", ?";
		}

		#endregion

		#region Command Object

		private void InitializeCommandString()
		{
			this.m_commandString = "{call " + this.m_stpName + " (?";
		}

		public void SetSTPName(string stpName)
		{
			this.m_stpName = stpName;
		}

		private void CreateCommandObject()
		{
			this.m_command = new OdbcCommand();
			m_command.CommandType = CommandType.StoredProcedure;
		}

		private void FinishCommandObject()
		{
			this.m_commandString = this.m_commandString + ")}";
			m_command.CommandText = this.m_commandString;
			m_command.Connection = settings.Connection;
		}

		#endregion

		#region Procedure execution

		/// <summary>
		/// Executes the Stored Procedure to the Database
		/// </summary>
		/// <returns>True if an error occured</returns>
		public bool Execute()
		{
			FinishCommandObject();

			try
			{
				settings.Connection.Open();
				m_command.ExecuteNonQuery();
			}
			catch (OdbcException objError)
			{
				//ActionLogger.LoggError("STP execute error at " + m_stpName, "Querry.Execute()", objError.Message.ToString());
				//if (!silentTransaction)
				//    Gencode.Common.Util.ErrorHandler.ShowError("Es ist ein Fehler beim ausführen in Prozedur " + m_stpName + " aufgetreten\n" + objError.Message.ToString(), "Fehler");

				throw new PersonalplanerException(string.Format("STP execute error at {0} Querry.Execute()\n{1}", m_stpName, objError.ToString()), objError);
			}
			finally
			{
				settings.Connection.Close();
			}

			return true;
		}

        /// <summary>
        /// Executes the Stored Procedure to the Database
        /// </summary>
        /// <param name="loggMessage">log message?</param>
        /// <returns>True if an error occured</returns>
        public bool Execute(bool loggMessage)
        {
            if (loggMessage)
                ActionLogger.LoggMessage(MessageType.SQL, m_loggstring);

            return Execute();
        }

		/// <summary>
		/// Executes the Procedure and returns all values in a DataTable
		/// </summary>
		/// <param name="loggMessage">Make an entry in the logfile</param>
		/// <returns>DataTable with all data from the Stored Procedure</returns>
		public DataTable ExecuteForDataTable(bool loggMessage = false)
		{
			if (loggMessage)
				ActionLogger.LoggMessage(MessageType.SQL, m_loggstring);

			OdbcDataReader reader;
			var dt = new DataTable();
			this.FinishCommandObject();

			try
			{
				settings.Connection.Open();

				reader = m_command.ExecuteReader();

                DataReaderAdapter dra = new DataReaderAdapter();
                dra.FillFromReader(dt, reader);
                //dv = new DataView(dt);
			}
			catch (Exception objError)
			{
				//Gencode.Common.Util.ErrorHandler.LoggError("STP execute error at " + m_stpName, "Querry.ExecuteForDataTable()", objError.Message.ToString());
				//Gencode.Common.Util.ErrorHandler.ShowError("Es ist ein Fehler beim ausführen in Prozedur " + m_stpName + " aufgetreten\n" + objError.Message.ToString(), "Fehler");

				throw new PersonalplanerException(string.Format("STP execute error at {0} Querry.ExecuteForDataTable()\n{1}", m_stpName, objError.ToString()), objError);
			}
			finally
			{
				settings.Connection.Close();
			}
			return dt;
		}

		#endregion

	}
}
